/**
 * 学生相关路由
 */

const express = require('express');
const router = express.Router();
const { db } = require('../db');
const { authenticateToken } = require('./auth');

// 中间件：检查是否有权限访问学生信息
const checkStudentAccess = (req, res, next) => {
  const { role, student_id } = req.user;
  const requestedStudentId = parseInt(req.params.id);

  // 管理员可以访问所有学生信息
  if (role === 'admin') {
    return next();
  }

  // 学生只能访问自己的信息
  if (role === 'student' && student_id === requestedStudentId) {
    return next();
  }

  // 教师可以访问选修了自己课程的学生信息
  if (role === 'teacher') {
    db.get(
      `SELECT 1 FROM enrollments e
       JOIN courses c ON e.course_id = c.id
       WHERE e.student_id = ? AND c.teacher_id = ?`,
      [requestedStudentId, req.user.teacher_id],
      (err, result) => {
        if (err) {
          return res.status(500).json({ error: '数据库查询错误' });
        }
        if (result) {
          return next();
        }
        res.status(403).json({ error: '无权访问该学生信息' });
      }
    );
    return;
  }

  res.status(403).json({ error: '无权访问该学生信息' });
};

// 获取学生列表
router.get('/', authenticateToken, (req, res) => {
  const { role } = req.user;
  let query = `
    SELECT 
      s.id, s.student_id, s.name, s.gender, s.age, 
      s.class, s.department, s.major
    FROM students s
  `;

  // 如果是教师，只返回选修了该教师课程的学生
  if (role === 'teacher') {
    query = `
      SELECT DISTINCT
        s.id, s.student_id, s.name, s.gender, s.age, 
        s.class, s.department, s.major
      FROM students s
      JOIN enrollments e ON s.id = e.student_id
      JOIN courses c ON e.course_id = c.id
      WHERE c.teacher_id = ?
    `;
  }

  const params = role === 'teacher' ? [req.user.teacher_id] : [];

  db.all(query, params, (err, students) => {
    if (err) {
      return res.status(500).json({ error: '获取学生列表失败', details: err.message });
    }
    res.json({ students });
  });
});

// 获取单个学生信息
router.get('/:id', authenticateToken, checkStudentAccess, (req, res) => {
  db.get(
    `SELECT 
      s.id, s.student_id, s.name, s.gender, s.age, 
      s.class, s.department, s.major,
      u.username, u.created_at
    FROM students s
    JOIN users u ON s.user_id = u.id
    WHERE s.id = ?`,
    [req.params.id],
    (err, student) => {
      if (err) {
        return res.status(500).json({ error: '获取学生信息失败', details: err.message });
      }
      if (!student) {
        return res.status(404).json({ error: '学生不存在' });
      }
      res.json({ student });
    }
  );
});

// 更新学生信息
router.put('/:id', authenticateToken, checkStudentAccess, (req, res) => {
  const { name, gender, age, class: className, department, major } = req.body;
  const studentId = req.params.id;

  db.run(
    `UPDATE students 
     SET name = ?, gender = ?, age = ?, class = ?, department = ?, major = ?
     WHERE id = ?`,
    [name, gender, age, className, department, major, studentId],
    function(err) {
      if (err) {
        return res.status(500).json({ error: '更新学生信息失败', details: err.message });
      }
      if (this.changes === 0) {
        return res.status(404).json({ error: '学生不存在' });
      }
      res.json({ message: '学生信息更新成功' });
    }
  );
});

// 获取学生的选课记录
router.get('/:id/enrollments', authenticateToken, checkStudentAccess, (req, res) => {
  db.all(
    `SELECT 
      e.id as enrollment_id, e.enrollment_date, e.grade, e.status,
      c.id as course_id, c.course_id as course_number, c.name as course_name,
      c.credit, c.hours, c.semester,
      t.name as teacher_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.id
    JOIN teachers t ON c.teacher_id = t.id
    WHERE e.student_id = ?
    ORDER BY e.enrollment_date DESC`,
    [req.params.id],
    (err, enrollments) => {
      if (err) {
        return res.status(500).json({ error: '获取选课记录失败', details: err.message });
      }
      res.json({ enrollments });
    }
  );
});

// 获取学生的课程表
router.get('/:id/schedule', authenticateToken, checkStudentAccess, (req, res) => {
  const semester = req.query.semester || '2023-2024-1'; // 默认当前学期

  db.all(
    `SELECT 
      c.id as course_id, c.course_id as course_number, c.name as course_name,
      cs.day_of_week, cs.start_time, cs.end_time, cs.classroom,
      t.name as teacher_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.id
    JOIN course_schedules cs ON c.id = cs.course_id
    JOIN teachers t ON c.teacher_id = t.id
    WHERE e.student_id = ? AND c.semester = ? AND e.status = 'enrolled'
    ORDER BY cs.day_of_week, cs.start_time`,
    [req.params.id, semester],
    (err, schedule) => {
      if (err) {
        return res.status(500).json({ error: '获取课程表失败', details: err.message });
      }
      res.json({ schedule });
    }
  );
});

module.exports = router;
